package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.BlogGrade;
import com.study.manage.domain.enumm.LiveDieState;

public interface BlogGradeRepository
        extends CrudRepository<BlogGrade, Long>, JpaSpecificationExecutor<BlogGrade> {

    @Query("select avg(t.point)/10 from BlogGrade t where t.state=0 and t.blogId=:blogId and (t.isEvaluateJury=:isEvaluateJury or :isEvaluateJury is null)")
    Float sumPointByBlogIdAndIsEvaluateJury(@Param("blogId") Long blogId,
            @Param("isEvaluateJury") Boolean isEvaluateJury);

    int countByBlogIdAndIsEvaluateJury(Long blogId, Boolean isEvaluateJury);

    @Query("select count(1) from BlogGrade t where t.evaluateId in (:evaluateIds) and t.userId=:userId and t.state=:state and (DATE_FORMAT(t.createdAt, '%Y%m') = DATE_FORMAT(:date, '%Y%m'))")
    int countByUserIdAndStateAndMoth(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("userId") Long userId, @Param("state") LiveDieState state,
            @Param("date") Date date);

    @Query("select t from BlogGrade t where t.evaluateId in (:evaluateIds) and t.userId=:userId and t.state=:state and (DATE_FORMAT(t.createdAt, '%Y%m') = DATE_FORMAT(:date, '%Y%m'))")
    Page<BlogGrade> findByUserIdAndStateAndMoth(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("userId") Long userId, @Param("state") LiveDieState state,
            @Param("date") Date date, Pageable pageable);


    @Query("select count(1) from BlogGrade t where t.blogId=:blogId and t.isEvaluateJury=:isEvaluateJury and t.state=0 and t.createdAt<:date")
    int countByBlogIdAndIsEvaluateJuryAndStateAndMinData(@Param("blogId") Long blogId,
            @Param("isEvaluateJury") Boolean isEvaluateJury, @Param("date") Date date);

}
